﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using WebApplication1.Common;
using WebApplication1.Models;
using System.Text.RegularExpressions;

namespace WebApplication1.BLL
{
    public class SchoolBLL
    {
        /// <summary>
        /// 根据主健查询学校简介
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <param name="checkLogin">1表示存在该用户，0表示不存在</param>
        /// <returns></returns>
        public object GetSchoolIntrodution(string schoolId)
        {
            string sql = "SELECT [schoolId],[schoolIntroduce],[schoolName] FROM [T_School] WHERE [schoolId]=@schoolId";
            SqlParameter[] pames = new SqlParameter[] {
                new SqlParameter("@schoolId",schoolId)
            };
            object dt = SQLHepler.GetDataSet(sql, pames).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据学校id，日期范围查询宝宝食谱(一周的食谱)
        /// </summary>
        /// <param name="schoolId">学校id</param>
        /// <returns></returns>
        public List<DateWeekTxt> GetRicepe(string schoolId, string date1, string date2)
        {
            string sql = "  SELECT r.[id],[footName],r.[type] as typeId,d.[name] as type,[startDate],[schoolId],[footImg] FROM [T_recipe] as r left join [T_Dictionary] as d on d.id=r.type WHERE [schoolId]=@schoolId and startDate>=@date1 and startDate<=@date2 order by [startDate] asc,[typeId] asc ";
            SqlParameter[] pames = new SqlParameter[] {
                new SqlParameter("@schoolId",schoolId),
                new SqlParameter("@date1",date1),
                new SqlParameter("@date2",date2)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, pames).Tables[0];
            List<RepiceModel> repiceList = new List<RepiceModel>();
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    RepiceModel model = new RepiceModel();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.footName = item["footName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["footName"]);
                    model.type = item["type"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["type"]);
                    string startDateOLD = item["startDate"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["startDate"]);
                    if (startDateOLD != "")
                    {
                        model.startDate = DateTime.Parse(startDateOLD).ToString("yyyy-MM-dd");
                    }
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    if (item["footImg"].GetType() != typeof(System.DBNull))
                    {
                        string footimg = Convert.ToString(item["footImg"]);
                        if (string.IsNullOrEmpty(footimg) == false && string.IsNullOrWhiteSpace(footimg) == false)
                        {
                            if (footimg.IndexOf("teacher") != -1)
                            {
                                string fileName = System.IO.Path.GetFileName(footimg);
                                string truthFileName = footimg.Replace(fileName, "small_" + fileName);
                                model.footImg = truthFileName;
                            }
                            else
                            {
                                model.footImg = footimg;
                            }
                        }
                    }
                    //else
                    //{
                    //    model.footImg = "img/biaoqing/8.png";
                    //}
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    repiceList.Add(model);
                }
            }

            //查询有几个星期在上课
            string sql2 = "select distinct([startDate]) FROM [T_recipe] WHERE [schoolId]=@schoolId and startDate>=@date1 and startDate<=@date2 order by [startDate] asc ";
            DataTable dt2 = SQLHepler.GetDataSet(sql2, pames).Tables[0];
            List<DateWeekTxt> list = new List<DateWeekTxt>();
            if (dt2 != null && dt2.Rows.Count > 0)
            {
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    var item = dt2.Rows[i];
                    if (item["startDate"].GetType() != typeof(System.DBNull))
                    {
                        string date = Convert.ToString(item["startDate"]);
                        DateTime date99 = Convert.ToDateTime(date);
                        var kkmm = (int)date99.DayOfWeek;    //星期天为0                       
                        string dateWeek = ((EnumWeek)kkmm).ToString();//得到星期描述文本，比如“星期天”
                        DateWeekTxt model = new DateWeekTxt();
                        model.date = date99.ToString("yyyy-MM-dd");
                        model.dateWeekTxt = dateWeek;
                        List<object> ondDayList = new List<object>();
                        //寻找出一天的食餐记录
                        if (repiceList != null)
                        {
                            for (int j = 0; j < repiceList.Count; j++)
                            {
                                var item2 = repiceList[j];
                                if (model.date.Equals(item2.startDate))
                                {
                                    ondDayList.Add(item2);
                                }
                            }
                        }
                        model.repiceList = ondDayList;
                        list.Add(model);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 根据学校id，日期范围查询宝宝课程(一周的课程)
        /// </summary>
        /// <param name="schoolId">学校id</param>
        /// <returns></returns>
        public List<DateWeekTxt> GetCourseList(string schoolId, string date1, string date2)
        {
            string sql = "  SELECT [id],[courseName],[type],[startDate],[schoolId] FROM [dbo].[T_Course]"
                        + " WHERE [schoolId]=@schoolId and startDate>=@date1 and startDate<=@date2 order by [startDate] asc,[type] ASC";
            SqlParameter[] pames = new SqlParameter[] {
                new SqlParameter("@schoolId",schoolId),
                new SqlParameter("@date1",date1),
                new SqlParameter("@date2",date2)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, pames).Tables[0];
            List<CourseModel> courseList = new List<CourseModel>();
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    CourseModel model = new CourseModel();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    string courseTxt = item["courseName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["courseName"]);
                    if (courseTxt != "")
                    {
                        model.courseName = courseTxt.Replace('#', ' ');
                    }
                    model.type = item["type"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["type"]);
                    string startDateOLD = item["startDate"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["startDate"]);
                    if (startDateOLD != "")
                    {
                        model.startDate = DateTime.Parse(startDateOLD).ToString("yyyy-MM-dd");
                    }
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    courseList.Add(model);
                }
            }
            //查询有几个星期在上课
            string sql2 = "select distinct([startDate]) FROM [T_Course] WHERE [schoolId]=@schoolId and startDate>=@date1 and startDate<=@date2 order by [startDate] asc ";
            DataTable dt2 = SQLHepler.GetDataSet(sql2, pames).Tables[0];
            List<DateWeekTxt> list = new List<DateWeekTxt>();
            if (dt2 != null && dt2.Rows.Count > 0)
            {
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    var item = dt2.Rows[i];
                    if (item["startDate"].GetType() != typeof(System.DBNull))
                    {
                        string date = Convert.ToString(item["startDate"]);
                        DateTime date99 = Convert.ToDateTime(date);
                        var kkmm = (int)date99.DayOfWeek;    //星期天为0                       
                        string dateWeek = ((EnumWeek)kkmm).ToString();//得到星期描述文本，比如“星期天”
                        DateWeekTxt model = new DateWeekTxt();
                        model.date = date99.ToString("yyyy-MM-dd");
                        model.dateWeekTxt = dateWeek;
                        List<object> ondDayList = new List<object>();
                        //寻找出一天的食餐记录
                        if (courseList != null)
                        {
                            for (int j = 0; j < courseList.Count; j++)
                            {
                                var item2 = courseList[j];
                                if (model.date.Equals(item2.startDate))
                                {
                                    ondDayList.Add(item2);
                                }
                            }
                        }
                        model.repiceList = ondDayList;
                        list.Add(model);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 查询校园新闻
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public object GetNews(int pageIndex, int pageSize, string userId, string schoolId)
        {
            schoolId = Common.SqlFilter.Filter(schoolId);
            string where = "[schoolId]='" + schoolId + "'";
            DataTable dt = SQLHepler.GetPaging("id", "[creatTime] desc", "[dbo].[T_SchoolNews]", where, pageIndex, pageSize);
            List<NewsModel> list = new List<NewsModel>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    NewsModel model = new NewsModel();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.title = item["title"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["title"]);
                    model.contentWZ = item["contentWZ"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["contentWZ"]);
                    if (!string.IsNullOrEmpty(model.contentWZ) && !string.IsNullOrWhiteSpace(model.contentWZ))
                    {
                        //清除html标签
                        string content = WebApplication1.Common.TxtCommon.ClearHtml(model.contentWZ);
                        int jq_len = content.Length >= 25 ? 25 : content.Length;
                        model.contentWZ = content.Substring(0, jq_len);
                    }
                    model.source = item["source"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["source"]);
                    model.creatTime =Convert.ToDateTime(item["creatTime"]).ToString("yyyy-MM-dd HH:mm");
                    if (item["headImgURL"].GetType() != typeof(System.DBNull))
                    {
                        string imgsrc = Convert.ToString(item["headImgURL"]);
                        model.class2 = "item item-thumbnail-left";//有图片li样式
                        if (string.IsNullOrEmpty(imgsrc) == false && string.IsNullOrWhiteSpace(imgsrc) == false)
                        {
                            if (imgsrc.IndexOf("teacher") != -1)
                            {
                                string fileName = System.IO.Path.GetFileName(imgsrc);
                                string truthFileName = imgsrc.Replace(fileName, "small_" + fileName);
                                model.headImgURL = truthFileName;
                            }
                            else
                            {
                                model.headImgURL = imgsrc; //"<img src=\"" + imgsrc + "\" style=\"height: 80px; width: 80px;\"/>";
                            }
                        }
                        if (imgsrc == "")
                        {
                            model.class2 = "item";//没有图片样式  
                            model.styleTxt = "display:none;";
                        }
                    }
                    else
                    {
                        model.class2 = "item";//没有图片样式  
                        model.styleTxt = "display:none;";
                    }
                    //阅读状态
                    int count = GetReadStatusNews(userId, model.id);
                    if (count == 1)
                    {
                        model.readStatus = "display:none;";//已阅读就隐藏
                    }
                    else
                    {
                        model.readStatus = "";
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 根据主键查询新闻
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetNewsById(string id, string teacherServerAddress)
        {
            string sql = "SELECT [id],[title],[contentWZ],[source],CONVERT(varchar(20),[creatTime],20) as creatTime,[headImgURL],[img] FROM [dbo].[T_SchoolNews] WHERE [id]=@id";
            SqlParameter parame = new SqlParameter("@id", id);
            DataTable dt = SQLHepler.GetDataSet(sql, parame).Tables[0];
            //解析发布的图片
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    string imgs = dt.Rows[0]["img"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dt.Rows[0]["img"]);
                    if (string.IsNullOrEmpty(imgs) == false && string.IsNullOrWhiteSpace(imgs) == false)
                    {
                        string imgsDiv = string.Empty;
                        string[] imgsArray = imgs.Split(',');
                        foreach (var item in imgsArray)
                        {
                            string truthURL = teacherServerAddress + item;
                            imgsDiv += "<img src=\"" + truthURL + "\" onerror=\"notShow(this)\" style=\"width:100%;text-align:center;margin-top:5px;margin-bottom:5px;\" />";
                        }
                        //填充到contentWZ末尾
                        dt.Rows[0]["contentWZ"] = dt.Rows[0]["contentWZ"] + imgsDiv;
                    }
                }
            }

            return dt;
        }

        /// <summary>
        /// 分页查询活动和通知
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="schoolId">学校id</param>
        /// <returns></returns>
        public object GetActiveList(int pageIndex, int pageSize, string schoolId, string userId)
        {
            string sqlwhere = "";
            if (string.IsNullOrEmpty(schoolId) == false)
            {
                schoolId = Common.SqlFilter.Filter(schoolId);
                sqlwhere = " [schoolId]='" + schoolId + "'";
            }
            DataTable dt = SQLHepler.GetPaging("id", "[creatTime] desc", "[dbo].[T_Notification]", sqlwhere, pageIndex, pageSize);
            List<Notification> list = new List<Notification>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    Notification model = new Notification();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.title = item["title"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["title"]);
                    model.source = item["source"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["source"]);
                    model.contentWZ = item["contentWZ"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["contentWZ"]);
                    if (!string.IsNullOrEmpty(model.contentWZ) && !string.IsNullOrWhiteSpace(model.contentWZ))
                    {
                        string content = Common.TxtCommon.ClearHtml(model.contentWZ);
                        int jq_len = content.Length >= 35 ? 35 : content.Length;
                        model.jieshaoTxt = content.Substring(0, jq_len);
                    }
                    model.creatTime = Convert.ToDateTime(item["creatTime"]).ToString("yyyy-MM-dd HH:mm");
                    if (item["coverImgURL"].GetType() != typeof(System.DBNull))
                    {
                        string imgURL = Convert.ToString(item["coverImgURL"]);
                        if (string.IsNullOrEmpty(imgURL) || string.IsNullOrWhiteSpace(imgURL))
                        {
                            model.class2 = "item";//没有图片样式  
                            model.styleTxt = "display:none;";
                        }
                        else
                        {
                            if (imgURL.IndexOf("teacher") != -1)
                            {
                                string fileName = System.IO.Path.GetFileName(imgURL);
                                string truthFileName = imgURL.Replace(fileName, "small_" + fileName);
                                model.coverImgURL = truthFileName;
                            }
                            else
                            {
                                model.coverImgURL = imgURL;
                            }
                            model.class2 = "item item-thumbnail-left";//有图片li样式
                        }
                    }
                    else
                    {
                        model.class2 = "item";//没有图片样式  
                        model.styleTxt = "display:none;";
                    }
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    ////去掉html标签
                    //string stroutput = model.contentWZ;
                    //if (string.IsNullOrEmpty(stroutput) == false)
                    //{
                    //    Regex regex = new Regex(@"<[^>]+>|</[^>]+>");
                    //    stroutput = regex.Replace(stroutput, "");
                    //    model.jieshaoTxt = stroutput.Substring(0, 50);
                    //}
                    //查询阅读状态
                    int readStatus = GetReadStatusNoticeActivity(userId, model.id);
                    if (readStatus == 1)
                    {
                        model.readStatus = "display:none;";
                    }
                    else
                    {
                        model.readStatus = "";
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 根据主键查询通知活动
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetActivityId(string id, string bsServerAddress)
        {
            string sql = "SELECT [id],[title],[contentWZ],[source],CONVERT(varchar(20),[creatTime],20) as creatTime,[schoolId] FROM [dbo].[T_Notification] WHERE [id]=@id";
            SqlParameter parame = new SqlParameter("@id", id);
            DataTable dt = SQLHepler.GetDataSet(sql, parame).Tables[0];
            //内容处理图片路径
            if (string.IsNullOrEmpty(bsServerAddress) == false && string.IsNullOrWhiteSpace(bsServerAddress) == false)
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    string content = dt.Rows[0]["contentWZ"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dt.Rows[0]["contentWZ"]);
                    if (content.IndexOf("/manager/ueditorImg") != -1)
                    {
                        string imgserverurl = bsServerAddress + "/manager/ueditorImg";
                        content = content.Replace("/manager/ueditorImg", imgserverurl);
                    }
                    dt.Rows[0]["contentWZ"] = content;
                }
            }
            return dt;
        }

        /// <summary>
        /// 根据学校id和家长用户id查询有权限的视频列表
        /// </summary>
        /// <param name="schoolId">学校id</param>
        /// <param name="jiazId">家长用户id</param>
        /// <returns></returns>
        public object GetVideoList(string schoolId, string jiazId)
        {
            string sql = "SELECT [id],[videoURL],[coverImgURL],[classId],[videoTitle],[schoolId],[deviceSerialNo] FROM [dbo].[T_VideoLive] WHERE [classId]=(select classId from T_Student where studentId=(select stuId from T_JiaZhang where jzId=@jiazId)) and [classId] IS not NULL OR [classId]='ALL' and Len([classId])<>0 AND [state]='1' AND [schoolId]=@schoolId";
            SqlParameter[] parames = new SqlParameter[]
            {
                new SqlParameter("@schoolId",schoolId),
                new SqlParameter("@jiazId",jiazId)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, parames).Tables[0];
            return dt;
        }

        /// <summary>
        /// 互动聊天保存发送的消息
        /// </summary>
        /// <param name="receivePersonId"></param>
        /// <param name="sendPersonId"></param>
        /// <param name="words"></param>
        /// <param name="schoolId"></param>
        /// <returns></returns>
        public int AddMsg(string receivePersonId, string userId, string words, string schoolId, out JiaoLiu outmodel)
        {
            string sql = "INSERT INTO [dbo].[T_Jiaoliu]([id],[words],[sendPersonId],[receivePersonId],[creatTime],[schoolId],[readState]) VALUES(@id,@words,@userId,@receivePersonId,@creatTime,@schoolId,0)";
            string id = Guid.NewGuid().ToString();
            DateTime creatime = DateTime.Now;
            SqlParameter[] parames =
            {
                new SqlParameter("@id",id),
                new SqlParameter("@words",words),
                new SqlParameter("@userId",userId),
                new SqlParameter("@receivePersonId",receivePersonId),
                new SqlParameter("@creatTime",DateTime.Now),
                new SqlParameter("@schoolId",schoolId)
            };
            int count = SQLHepler.ExecuteSql(sql, parames);
            outmodel = new JiaoLiu();
            outmodel.id = id;
            outmodel.type = "me";
            //string wordsHtml = ReplaceBqImg(words);
            //outmodel.words = wordsHtml;
            outmodel.words = words;
            outmodel.creatTime = creatime.ToString("yyyy-MM-dd HH:mm:ss");
            JiaZhang user = new UserInfoBLL().GetJiaZhang(userId);
            outmodel.headImgURL = user.headImgURL;
            //outmodel.headImgURL = "img/qqqq.jpg";
            //if (user != null)
            //{
            //    if (string.IsNullOrEmpty(user.headImgURL) == false)
            //    {
            //        outmodel.headImgURL = user.headImgURL;
            //    }
            //}
            return count;
        }

        ///// <summary>
        ///// 根据学生id查询孩子所属的老师id
        ///// </summary>
        ///// <param name="userId">用户id</param>
        ///// <returns></returns>
        //public string GetTeacherIdByUser(string studentId)
        //{
        //    string sql = "SELECT [teacherId] FROM [dbo].[T_TeacherClassRelation] where [classId]=(select [classId] from [T_Student] where [studentId]=@studentId) and [status]='可用'";
        //    SqlParameter parame = new SqlParameter("@studentId", studentId);
        //    DataTable dt = SQLHepler.GetDataSet(sql, parame).Tables[0];
        //    string teacherid = "";
        //    if (dt != null && dt.Rows.Count > 0)
        //    {
        //        teacherid = dt.Rows[0]["teacherId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dt.Rows[0]["teacherId"]);//教师id
        //    }
        //    return teacherid;
        //}

        /// <summary>
        /// 互动交流单个老师消息未读消息状态
        /// </summary>
        /// <param name="teacherId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public object GetMsgInfo(string teacherId, string userId)
        {
            StringBuilder sb = new StringBuilder("SELECT MAX([creatTime]) as lastTime,COUNT(id) AS msgCount FROM [dbo].[T_Jiaoliu] where [sendPersonId]=@teacherId and [receivePersonId]=@userId and [readState]='0'");
            SqlParameter[] parames = {
                new SqlParameter("@teacherId",teacherId),
                new SqlParameter("@userId",userId)
            };
            DataTable dt = SQLHepler.GetDataSet(sb.ToString(), parames).Tables[0];
            string lastTime = "";//最后回复时间
            int msgCount = 0;//未读消息个数
            if (dt != null && dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];
                if (row["lastTime"].GetType() != typeof(System.DBNull))
                {
                    lastTime = Convert.ToDateTime(row["lastTime"]).ToString("yyyy-MM-dd HH:mm");
                }
                if (row["msgCount"].GetType() != typeof(System.DBNull))
                {
                    msgCount = Convert.ToInt32(row["msgCount"]);
                }
            }
            //查询最新消息
            string sql = "SELECT top 1 [words],[creatTime] FROM [dbo].[T_Jiaoliu] where ([sendPersonId]=@teacherId and [receivePersonId]=@userId) or ([sendPersonId]=@userId and [receivePersonId]=@teacherId) order by creatTime desc";
            //object objMsg = SQLHepler.GetSingleValue(sql, parames);
            DataTable dt2 = SQLHepler.GetDataSet(sql, parames).Tables[0];
            string newMsg = "";
            string newTime = "";
            if (dt2 != null && dt2.Rows.Count > 0)
            {
                newMsg = dt2.Rows[0]["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dt2.Rows[0]["words"]);
                DateTime newTime2 = Convert.ToDateTime(dt2.Rows[0]["creatTime"]);
                DateTime now = DateTime.Today;
                if (newTime2.ToString("yyyy-MM-dd").Equals(now.ToString("yyyy-MM-dd")))
                {
                    newTime = newTime2.ToString("HH:mm");
                }
                else if (newTime2.ToString("yyyy-MM").Equals(now.ToString("yyyy-MM")) && (now.Day - newTime2.Day) == 1)
                {
                    newTime = "昨天" + newTime2.ToString("HH:mm");
                }
                else if (newTime2.ToString("yyyy-MM").Equals(now.ToString("yyyy-MM")) && (now.Day - newTime2.Day) == 2)
                {
                    newTime = "前天" + newTime2.ToString("HH:mm");
                }
                else
                {
                    newTime = newTime2.ToString("yyyy-MM-dd HH:mm");
                }
            }
            var data = new { lastTime = lastTime, msgCount = msgCount, newMsg = newMsg, newTime = newTime };
            return data;
        }

        /// <summary>
        /// 根据班级id查询教师列表
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetContractList(string classId)
        {
            string sql = "SELECT [id],[teacherName],[headImgURL],[type],[status] FROM [YouErYuanDB].[dbo].[T_Teacher] WHERE [id] IN(SELECT [teacherId] FROM [YouErYuanDB].[dbo].[T_TeacherClassRelation] where [classId]=@classId) and [status]='1'";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@classId", classId)).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                //获取最新一条消息
                //DataColumn columnNewestMsg = new DataColumn("newMsg");
                //dt.Columns.Add(columnNewestMsg);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    //item["newMsg"] = "什么情况？";//查询最新的一条记录
                    if (item["headImgURL"].GetType() == typeof(System.DBNull) || item["headImgURL"] == null || item["headImgURL"].ToString() == "")
                    {
                        dt.Rows[i]["headImgURL"] = "img/teacher.png";//没有头像，赋值个默认图片
                    }
                    else
                    {
                        string teacherImg = Convert.ToString(dt.Rows[i]["headImgURL"]);
                        string fileName = System.IO.Path.GetFileName(teacherImg);//头像文件名
                        string filePath = teacherImg.Replace(fileName, "small_" + fileName);//拼接small
                        dt.Rows[i]["headImgURL"] = filePath;
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 查询交流记录
        /// </summary>
        /// <param name="msgCount">最新的几条未读的消息个数</param>
        /// <param name="teacherId">教师id</param>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public object GetMsgList(int pageIndex, int pageSize, string teacherId, string userId, string serverAddress)
        {
            List<JiaoLiu> list = new List<JiaoLiu>();
            if (pageSize < 5)
            {
                pageSize = 5;
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(" select [id],[words],[sendPersonId],[receivePersonId],[creatTime],[schoolId],[readState] from (    ");
            sb.Append(" 	SELECT top " + pageSize + "  [id],[words],[sendPersonId],[receivePersonId],[creatTime],[schoolId],[readState]  ");
            sb.Append(" 	FROM [dbo].[T_Jiaoliu]  ");
            sb.Append(" 	WHERE id not in( ");
            sb.Append(" 		select top " + (pageIndex - 1) * pageSize + " id from [dbo].[T_Jiaoliu]  ");
            sb.Append("         where (sendPersonId=@teacherId AND [receivePersonId]=@userId)  ");
            sb.Append("         or (sendPersonId=@userId AND [receivePersonId]=@teacherId)  ");
            sb.Append("         order by [creatTime] desc ");
            sb.Append(" )  ");
            sb.Append(" and ((sendPersonId=@teacherId AND [receivePersonId]=@userId) or (sendPersonId=@userId AND [receivePersonId]=@teacherId))  ");
            sb.Append(" order by [creatTime] desc ");
            sb.Append(" ) as temp order by [creatTime] asc ");
            SqlParameter[] parames = {
                new SqlParameter("@teacherId",teacherId),
                new SqlParameter("@userId",userId)
            };
            DataTable dt = SQLHepler.GetDataSet(sb.ToString(), parames).Tables[0];
            //查询我（用户）的头像
            JiaZhang user = new UserInfoBLL().GetJiaZhang(userId);//查询学生头像
            //查询教师头像           
            Teacher teacher = GetTeacher(teacherId);
            string userHeadImgURL = "img/qqqq.jpg";//用户替代头像
            string teaHeadImgURL = "img/teacher.png";
            if (user != null)
            {
                if (string.IsNullOrEmpty(user.headImgURL) == false)
                {
                    //userHeadImgURL = serverAddress + user.headImgURL;
                    userHeadImgURL = user.headImgURL;
                }
            }
            if (teacher != null)
            {
                if (string.IsNullOrEmpty(teacher.headImgURL) == false)
                {
                    //teaHeadImgURL = serverAddress + teacher.headImgURL;                   
                    string fileName = System.IO.Path.GetFileName(teacher.headImgURL);//头像文件名
                    string filePath = teacher.headImgURL.Replace(fileName, "small_" + fileName);//拼接small
                    teaHeadImgURL = filePath;
                }
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    JiaoLiu model = new JiaoLiu();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    if (item["words"].GetType() != typeof(System.DBNull))
                    {
                        string words = Convert.ToString(item["words"]);
                        //words = ReplaceBqImg(words);
                        model.words = words;
                    }
                    model.sendPersonId = item["sendPersonId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sendPersonId"]);
                    model.receivePersonId = item["receivePersonId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["receivePersonId"]);
                    DateTime creatTime = item["creatTime"].GetType() == typeof(System.DBNull) ? Convert.ToDateTime("1990-01-01 00:00:00") : Convert.ToDateTime(item["creatTime"]);
                    model.creatTime = creatTime.ToString("yyyy-MM-dd HH:mm:ss");
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    model.readState = item["readState"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["readState"]);
                    if (string.IsNullOrEmpty(model.sendPersonId) == false)
                    {
                        if (model.sendPersonId == userId)
                        {
                            model.type = "me";
                            model.headImgURL = userHeadImgURL;
                        }
                        else
                        {
                            model.type = "te";
                            model.headImgURL = teaHeadImgURL;
                        }
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 替换表情为图片标签
        /// </summary>
        /// <param name="words"></param>
        /// <returns></returns>
        public string ReplaceBqImg(string words)
        {
            words = words.Replace("[@1_1]", "<img src =\"img/biaoqing/1.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_2]", "<img src =\"img/biaoqing/2.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_3]", "<img src =\"img/biaoqing/3.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_4]", "<img src =\"img/biaoqing/4.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_5]", "<img src =\"img/biaoqing/5.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_6]", "<img src =\"img/biaoqing/6.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_7]", "<img src =\"img/biaoqing/7.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_8]", "<img src =\"img/biaoqing/8.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_9]", "<img src =\"img/biaoqing/9.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_10]", "<img src =\"img/biaoqing/10.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_11]", "<img src =\"img/biaoqing/11.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_12]", "<img src =\"img/biaoqing/12.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_13]", "<img src =\"img/biaoqing/13.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_14]", "<img src =\"img/biaoqing/14.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_15]", "<img src =\"img/biaoqing/15.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_16]", "<img src =\"img/biaoqing/16.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_17]", "<img src =\"img/biaoqing/17.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_18]", "<img src =\"img/biaoqing/18.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_19]", "<img src =\"img/biaoqing/19.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_20]", "<img src =\"img/biaoqing/20.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_21]", "<img src =\"img/biaoqing/21.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_22]", "<img src =\"img/biaoqing/22.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_23]", "<img src =\"img/biaoqing/23.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_24]", "<img src =\"img/biaoqing/24.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_25]", "<img src =\"img/biaoqing/25.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_26]", "<img src =\"img/biaoqing/26.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_27]", "<img src =\"img/biaoqing/27.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_28]", "<img src =\"img/biaoqing/28.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_29]", "<img src =\"img/biaoqing/29.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_30]", "<img src =\"img/biaoqing/30.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_31]", "<img src =\"img/biaoqing/31.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_32]", "<img src =\"img/biaoqing/32.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_33]", "<img src =\"img/biaoqing/33.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_34]", "<img src =\"img/biaoqing/34.png\" class=\"biaoqing\" />");
            words = words.Replace("[@1_35]", "<img src =\"img/biaoqing/35.png\" class=\"biaoqing\" />");
            return words;
        }

        /// <summary>
        /// 修改消息状态，1=已读，0=未读
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="teachId"></param>
        /// <returns></returns>
        public int UpdateReadState(string userId, string teachId, string readyState)
        {
            string sql = "update [dbo].[T_Jiaoliu] set [readState]=@readyState WHERE (sendPersonId=@userId AND [receivePersonId]=@teachId) or (sendPersonId=@teachId AND [receivePersonId]=@userId)";
            SqlParameter[] parmaes = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@teachId",teachId),
                new SqlParameter("@readyState",readyState)
            };
            int count = SQLHepler.ExecuteSql(sql, parmaes);
            return count;
        }

        /// <summary>
        /// 获取签到日历数据，selecMonth格式2014-11
        /// </summary>
        /// <param name="schoolId">学校id</param>
        /// <param name="studentId">学生id</param>
        /// <param name="selecMonth">选中月份</param>
        /// <returns></returns>
        public object GetQianDaoRiLi(string schoolId, string studentId, string selecMonth)
        {
            //装签到的日期
            List<Qiandao> list = new List<Qiandao>();
            //string sql = "SELECT [id],CONVERT(varchar(20),[signTime], 8) as signTime2,CONVERT(varchar(100), [signTime], 23) as signTime1,[signImg],datepart(weekday,[signTime]) as wk,[type],[studentId],schoolId FROM [dbo].[T_qiandao] where SUBSTRING(CONVERT(varchar(100),[signTime],23),0,8)=@selecMonth and schoolid=@schoolId and [studentId]=@studentId order by [signTime] asc";
            //string sql = "SELECT [id],CONVERT(varchar(20),[signTime],8) as signTime2,CONVERT(varchar(100),[signTime],23) as signTime1,[signImg],datepart(weekday,[signTime]) as wk,[type],[studentId],schoolId from [YouErYuanDB].[dbo].[T_qiandao] where [signTime] in(SELECT max([signTime]) FROM [YouErYuanDB].[dbo].[T_qiandao] group by [type],CONVERT(varchar(20),[signTime],23)) and SUBSTRING(CONVERT(varchar(100),[signTime],23),0,8)=@selecMonth and schoolid=@schoolId and [studentId]=@studentId order by [signTime] asc";
            string sql = "select CONVERT(varchar(20),tt,8) as signTime2,CONVERT(varchar(20),tt,23) as signTimeDate,[type] from(SELECT max([signTime]) as tt,[type] FROM [dbo].[T_qiandao] where SUBSTRING(CONVERT(varchar(100),[signTime],23),0,8)=@selecMonth and schoolid=@schoolId and [studentId]=@studentId group by CONVERT(varchar(100),[signTime],23),[type]) as tempT order by tt asc";
            SqlParameter[] parames = {
                new SqlParameter("@schoolId", schoolId),
                new SqlParameter("@selecMonth", selecMonth),
                new SqlParameter("@studentId", studentId)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, parames).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    Qiandao model = new Qiandao();
                    model.signTimeDate = item["signTimeDate"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["signTimeDate"]);
                    model.signTime = item["signTime2"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["signTime2"]);

                    //model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    //model.wk = item["wk"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["wk"]);
                    //model.signImg = item["signImg"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["signImg"]);
                    //model.type = item["type"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["type"]);
                    //model.studentId = item["studentId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["studentId"]);
                    //model.id = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    list.Add(model);
                }
            }
            //一个月日历横向6个周
            List<object>[] weekArray = {
               new List<object>(),
               new List<object>(),
               new List<object>(),
               new List<object>(),
               new List<object>(),
               new List<object>()
            };
            //装一个月的每个星期       
            List<Qiandao> listDays = new List<Qiandao>();
            //计算这个月多少天
            DateTime dateym = Convert.ToDateTime(selecMonth);
            int thisMonthDays = DateTime.DaysInMonth(dateym.Year, dateym.Month);
            //如果有签到天数
            if (list.Count > 0)
            {
                //0表示空格，1表示签到，2表示未签到
                for (int i = 1; i <= thisMonthDays; i++)
                {
                    int indexTemp = i;
                    string day = indexTemp < 10 ? "0" + indexTemp : indexTemp.ToString();
                    string date = selecMonth + "-" + day;//以i为日的计算
                    int thisDayWeek = (int)Convert.ToDateTime(date).DayOfWeek;//判断这天星期几,星期天为0，星期六为6
                    if (indexTemp == 1)//表示这个月的第一天
                    {
                        //计算前面空格数
                        if (thisDayWeek != 0)//为星期天没有空格,非星期天有空格
                        {
                            for (int kk = 0; kk < thisDayWeek; kk++)
                            {
                                Qiandao model = new Qiandao() { dayhtmlType = "0", dayNumber = "" };
                                listDays.Add(model);
                            }
                        }
                    }
                    List<Qiandao> dayArr = list.FindAll(o => o.signTimeDate == date);
                    if (dayArr.Count > 0)//表示当前这天是签到的日
                    {
                        Qiandao model = dayArr[0];
                        model.dayhtmlType = "1";
                        model.dayNumber = indexTemp + "";
                        if (dayArr.Count == 2)//表示上午下午都是签到了
                        {
                            model.signTimeAm = dayArr[0].signTime.Substring(0, 5);//早上签到时间
                            model.signTimePm = dayArr[1].signTime.Substring(0, 5);//放学签到时间
                            model.signTimeDate = dayArr[0].signTimeDate;//日期
                            listDays.Add(model);
                        }
                        else if (dayArr.Count == 1)
                        {
                            DateTime signTime = Convert.ToDateTime(model.signTime);
                            DateTime zhongwu = Convert.ToDateTime("12:00:00");
                            int bizhi = signTime.CompareTo(zhongwu);
                            if (bizhi == -1)//早，表示上午签到
                            {
                                model.signTimeAm = dayArr[0].signTime.Substring(0, 5);
                                model.signTimePm = "未签到";
                            }
                            else//下午签到
                            {
                                model.signTimeAm = "未签到";
                                model.signTimePm = dayArr[0].signTime.Substring(0, 5);
                            }
                            model.signTimeDate = dayArr[0].signTimeDate;//日期
                            listDays.Add(model);
                        }
                    }
                    else
                    {   //未签到
                        Qiandao model = new Qiandao() { dayhtmlType = "2", dayNumber = indexTemp + "", signTimeDate = date };
                        listDays.Add(model);
                    }
                    //计算最后一天之后的空格
                    if (indexTemp == thisMonthDays)
                    {
                        int lastDayWeek = (int)Convert.ToDateTime(selecMonth + "-" + thisMonthDays).DayOfWeek;
                        if (lastDayWeek != 6)
                        {
                            int kgCount = 6 - lastDayWeek;//空格数
                            for (int kk = 0; kk < kgCount; kk++)
                            {
                                Qiandao model = new Qiandao() { dayhtmlType = "0", dayNumber = "" };
                                listDays.Add(model);
                            }
                        }
                    }
                }
                //将每天分配给每周的组
                int index = 0;
                for (int days = 0; days < listDays.Count; days++)
                {
                    var item = listDays[days];
                    weekArray[index].Add(item);
                    if (weekArray[index].Count == 7)
                    {
                        index = index + 1;
                    }
                }
                return weekArray;
            }
            return null;
        }

        /// <summary>
        /// 获取学生某天的签到记录
        /// </summary>
        /// <param name="studentId">学生id</param>
        /// <param name="date">哪一天</param>
        /// <returns></returns>
        public object GetQiandaoByDay(string studentId, string date)
        {
            string sql = "SELECT [id],[signTime],convert(varchar(30),signTime,20) as time2,[signImg],[type],s.[studentId],s.stuName,m.[schoolId],j.guanxi  FROM [dbo].[T_qiandao] as m left join T_Student as s on s.studentId=m.studentId left join T_Jiazhang as j on j.jzId=m.jzId where s.studentId=@studentId and convert(varchar(30),signTime,23)=@date order by signTime desc";
            SqlParameter[] parames = {
                 new SqlParameter("@date",date),
                 new SqlParameter("@studentId", studentId)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, parames).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                //查询该学生所在班级【签到视频查看截止日期】
                Student student = GetStudent(studentId);
                if (student != null)
                {
                    //获取班级截止日期
                    string qvEndDate = "";
                    GetQvEndDate obj = new AppAboutBLL().GetQvEndDate(student.ClassId, out qvEndDate);
                    if (obj.state == 0)//未到期，或未定义截止日期
                    {

                    }
                    else
                    {
                        if (string.IsNullOrEmpty(qvEndDate) == false)
                        {
                            //如果超过截止日期就将签到图片的值赋为空
                            DateTime qvEndDateDD = Convert.ToDateTime(qvEndDate).Date;
                            int bizhi = Convert.ToDateTime(date).Date.CompareTo(qvEndDateDD);
                            if (bizhi == 1)
                            {  //过期
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    dt.Rows[i]["signImg"] = "";//将签到图片的值赋为空
                                }
                            }
                        }
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 根据主键查询教师
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Teacher GetTeacher(string id)
        {
            string sql = "SELECT [id],[teacherName],[kahao],[sex],[phone],[address],[bornDay],[headImgURL] FROM [dbo].[T_Teacher] where id=@id ";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@id", id)).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var item = dt.Rows[0];
                Teacher model = new Teacher();
                model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                model.teacherName = item["teacherName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["teacherName"]);
                model.kahao = item["kahao"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["kahao"]);
                model.sex = item["sex"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sex"]);
                model.phone = item["phone"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["phone"]);
                model.address = item["address"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["address"]);
                model.bornDay = item["bornDay"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["bornDay"]);
                model.headImgURL = item["headImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["headImgURL"]);
                //if (item["headImgURL"].GetType() != typeof(System.DBNull))
                //{
                //    string img = Convert.ToString(item["headImgURL"]);
                //    if (string.IsNullOrEmpty(img) == false)
                //    {
                //        model.headImgURL = img;
                //    }
                //    else
                //    {
                //        model.headImgURL = "img/teacher.png";
                //    }
                //}
                return model;
            }
            return null;
        }

        /// <summary>
        /// 根据主键查询小学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Student GetStudent(string id)
        {
            string sql = "SELECT [studentId],[studentNo],[sex],[stuName],convert(varchar(20),[bornDate],23) as bornDate,[mingZu],[jiGuan],[email],[classId],[headImgURL],st.[schoolId],t.className,s.schoolName FROM [dbo].[T_Student] as st left join [dbo].[T_School] as s on s.schoolId=st.[schoolId] left join [dbo].[T_Class] as t on t.id=st.[classId] where [studentId]=@id ";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@id", id)).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var item = dt.Rows[0];
                Student model = new Student();
                model.StudentId = item["studentId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["studentId"]);
                model.StudentNo = item["studentNo"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["studentNo"]);
                model.Sex = item["sex"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sex"]);
                model.StuName = item["stuName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["stuName"]);
                model.BornDate = item["bornDate"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["bornDate"]);
                model.MingZu = item["mingZu"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["mingZu"]);
                model.JiGuan = item["jiGuan"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jiGuan"]);
                model.Email = item["email"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["email"]);
                model.ClassId = item["classId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["classId"]);
                model.HeadImgURL = item["headImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["headImgURL"]);
                model.SchoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                model.ClassName = item["className"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["className"]);
                model.SchoolName = item["schoolName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolName"]);
                if (item["headImgURL"].GetType() != typeof(System.DBNull))
                {
                    string img = Convert.ToString(item["headImgURL"]);
                    if (string.IsNullOrEmpty(img) == false)
                    {
                        model.HeadImgURL = img;
                    }
                    else
                    {
                        model.HeadImgURL = "img/qqqq.jpg";
                    }
                }
                if (string.IsNullOrEmpty(model.BornDate) == false)
                {
                    DateTime born = Convert.ToDateTime(model.BornDate);
                    TimeSpan datejiange = DateTime.Now.Subtract(born);
                    int jgDays = datejiange.Days;
                    model.Age = jgDays / 365;
                }
                else
                {
                    model.Age = 1;
                }
                return model;
            }
            return null;
        }

        /// <summary>
        /// 添加一条阅读通知和活动
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wzId"></param>
        /// <returns></returns>
        public int AddReadedNoticeActivity(string userId, string wzId)
        {
            string sql = "INSERT INTO [dbo].[T_ReadedNoticeActivity]([userId],[noticeActivityId],[creatTime]) VALUES(@userId,@noticeActivityId,@creatTime)";
            SqlParameter[] parmes = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@noticeActivityId",wzId),
                new SqlParameter("@creatTime",DateTime.Now),
            };
            int count = SQLHepler.ExecuteSql(sql, parmes);
            return count;
        }

        /// <summary>
        /// 根据用户id和通知文章id查询记录是否存在，存在返回1,否则返回0
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wzId"></param>
        /// <returns></returns>
        public int GetReadStatusNoticeActivity(string userId, string wzId)
        {
            string sql = "SELECT [id],[userId],[noticeActivityId],[creatTime] FROM [dbo].[T_ReadedNoticeActivity] where [userId]=@userId and [noticeActivityId]=@wzid";
            SqlParameter[] parames = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@wzid",wzId),
            };
            object obj = SQLHepler.GetSingleValue(sql, parames);
            if (obj != null)
            {
                return 1;
            }
            return 0;
        }

        /// <summary>
        /// 添加新闻阅读状态一条记录
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="newsId">新闻id</param>
        /// <returns></returns>
        public int AddNewsReaded(string userId, string newsId)
        {
            string sql = "INSERT INTO [dbo].[T_ReadedNews]([userId],[newsId],[creatTime]) VALUES(@userId,@newsId,@creatTime)";
            SqlParameter[] parmes = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@newsId",newsId),
                new SqlParameter("@creatTime",DateTime.Now),
            };
            int count = SQLHepler.ExecuteSql(sql, parmes);
            return count;
        }

        /// <summary>
        /// 根据用户id和通知文章id查询记录是否存在，存在返回1,否则返回0
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wzId"></param>
        /// <returns></returns>
        public int GetReadStatusNews(string userId, string newsId)
        {
            string sql = "SELECT [id],[userId],[newsId],[creatTime] FROM [dbo].[T_ReadedNews] where [userId]=@userId and [newsId]=@newsId";
            SqlParameter[] parames = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@newsId",newsId),
            };
            object obj = SQLHepler.GetSingleValue(sql, parames);
            if (obj != null)
            {
                return 1;
            }
            return 0;
        }

        /// <summary>
        /// 统计没有阅读的新闻、通知个数
        /// </summary>
        /// <returns></returns>
        public int[] GetNoReadCount(string userId, string schoolId)
        {
            string sql = "SELECT COUNT(1) FROM [dbo].[T_SchoolNews] where [id] not in (select newsId from [dbo].[T_ReadedNews]  where userId=@userId) and schoolId=@schoolId ";
            string sql2 = "SELECT count(1) FROM [dbo].[T_Notification] where id not in(select [noticeActivityId] from [dbo].[T_ReadedNoticeActivity] where [userId]=@userId) and [schoolId]=@schoolId";
            SqlParameter parme = new SqlParameter("@userId", userId);
            SqlParameter[] parames1 =
            {
                new SqlParameter("@userId", userId),
                new SqlParameter("@schoolId", schoolId),
            };

            SqlParameter[] parames =
            {
                new SqlParameter("@userId", userId),
                new SqlParameter("@schoolId", schoolId),
             };
            object obj = SQLHepler.GetSingleValue(sql, parames1);
            object obj2 = SQLHepler.GetSingleValue(sql2, parames);
            int[] arrCount = { 0, 0 };
            if (obj != null)
            {
                arrCount[0] = Convert.ToInt32(obj);//新闻没有阅读的个数
            }
            if (obj2 != null)
            {
                arrCount[1] = Convert.ToInt32(obj2);//通知没有阅读的个数
            }
            return arrCount;
        }

    }
}